﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffIdInPositionToViewClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffIdInPositionToViewClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffIdInPositionToViewClass]  
    @ClassID int ,
    @Act_ID int
/*

============================================================
功能:    得到职位中能对@ClassID进行@Act_ID操作的成员
参数:
    @ClassID int         :    组（类）ID
    @Act_ID int        :    浏览权代号
备注:    可以优化
============================================================

*/
AS
declare @team_ID int
declare @parent_id int
declare @IDS nvarchar(255)
declare @team_IDS nvarchar(255)
declare @Sql nvarchar(2000)


--Set @ClassID = 1
Set @parent_id=0
Set @IDS =''
Set @team_IDS =''
Set @Sql=N'select staff_ID from uds_staff where 1=2'

--到拥有此权限所在职位的ID
Select @team_IDS= @team_IDS + convert(nvarchar,Position_ID) + ',' from UDS_Assign_Rule a,uds_class b where b.classid = a.team_id and b.classid =@ClassID and based_on =1 and a.Act_ID = @Act_id
print @team_IDS
While Len(@team_IDS)>0        
Begin
    
    Set @team_id = convert(int,substring(@team_ids,1,charindex(',',@team_ids)-1))
    Select @team_IDS = substring(@team_IDS,charindex(',',@team_ids)+1,len(@team_ids))
    Select @parent_id=Super_Position_ID from uds_Position where Position_ID=@team_id
    Set @IDS=convert(nvarchar,@team_id)
    While @parent_id<>@team_id
    Begin
        Set @IDS = @IDS + ',' + convert(nvarchar,@parent_id)   
        Set @team_id = @parent_id
        Select @parent_id=Super_Position_ID from uds_Position where Position_ID=@team_id
    
    End 

    If len(@ids)>0    
        Set @Sql =@sql + N' union select staff_ID from uds_staff_in_position where Position_ID in (' + @ids  + ')'
End

Execute sp_executesql @sql